-- @owner: opentestcase023
-- @date: 2022/02/24
-- @testpoint: 当存储过程在src表中插入值之前，向des表中插入值

--step1:建表;expect:成功
drop table if exists t_trigger_src_00073 cascade;
drop table if exists t_trigger_des_00073 cascade;
create table t_trigger_src_00073(id1 int, id2 int, id3 int) with (storage_type=ustore);
create table t_trigger_des_00073(id1 int, id2 int, id3 int) with (storage_type=ustore);

--step2:当存储过程在src表中插入值之前，向des表中插入值 each row;expect:成功
create or replace function f_ustore_trigger_case0071() returns trigger as
$$
declare
begin
insert into t_trigger_des_00073 values(new.id1, new.id2, new.id3);
return new;
end
$$ language plpgsql;
/
drop trigger if exists insert_trigger on t_trigger_src_00073 cascade;
create trigger insert_trigger before insert on t_trigger_src_00073
        for each row
		when(new.id1<1000)
		execute procedure f_ustore_trigger_case0071();
/
select * from t_trigger_des_00073;
create or replace procedure proc_while_loop(maxval in integer)
as
    declare
    i int :=1;
    begin
        while i < maxval loop
            insert into t_trigger_src_00073 values(i,i,i);
            i:=i+1;
        end loop;
    end;
/
call proc_while_loop(100);
select * from t_trigger_des_00073;

--step3:清理环境;expect:成功
drop trigger if exists insert_trigger on t_trigger_src_00073 cascade;
drop table if exists t_trigger_src_00073 cascade;
drop table if exists t_trigger_des_00073 cascade;
drop function if exists f_ustore_trigger_case0071;